<?php
// $Id$

include_once('BaseModel.class.php');

class MessageTrackingManagerModel extends BaseModel
{
    function collect($offset=null,$limit=null,$search=array(),$by='1',$sort='',$u_id='',$u_prv='')
	{
		$where = ($search) ? "WHERE LOWER({$search['searchfield']}) LIKE '%{$search['searchtext']}%'" : "WHERE 1";

		if (is_null($offset)) {
            
	        $query = "SELECT COUNT(*) FROM messages msg $where";
		} else {
	        $query = "SELECT msg.id,msg.message,
                                 COUNT(*) as contacts, 
                                 SUM(IF(otbx.status='pending',1,0)) as pending, 
                                 SUM(IF(otbx.status='success',1,0)) as success,
                                 SUM(IF(otbx.status='reject',1,0)) as reject,
                                 SUM(IF(otbx.status='failure',1,0)) as failure,
                                 SUM(IF(otbx.status='buffered',1,0)) as buffered 
                           FROM `outbox` otbx JOIN messages msg ON msg.id = otbx.message_id
                           WHERE otbx.active = 1 
                           GROUP BY msg.id ORDER BY $by $sort LIMIT $offset,$limit";

		}
		return $this->query($query, true);
    }

    function datalist($search){
		$where = ($search) ? "WHERE LOWER({$search['searchfield']}) LIKE '%{$search['searchtext']}%'" : "";
	        $query = "SELECT msg.id,msg.message,
                                 COUNT(*) as contacts, 
                                 SUM(IF(otbx.status='pending',1,0)) as pending, 
                                 SUM(IF(otbx.status='success',1,0)) as success,
                                 SUM(IF(otbx.status='reject',1,0)) as reject,
                                 SUM(IF(otbx.status='failure',1,0)) as failure,
                                 SUM(IF(otbx.status='buffered',1,0)) as buffered 
                           FROM `outbox` otbx JOIN messages msg ON msg.id = otbx.message_id $where 
                           GROUP BY msg.id ";

		return $this->query($query, true);
    }
    function detail ($id)
    {
        $query = "SELECT * FROM inbox WHERE id='$id'";
        return $this->query($query, true);
    }

    /*
     * Add members and return the new member_id
     *
     */
    function ins($detail)
    {
        return $this->insert('inbox', $detail);
        return $this->last_insert_id('inbox');
    }

    /*
     * Update categories for a particular categories_id
     *
     */
    function edit ($id, $detail)
    {
        return $this->update('inbox', $detail, "id = '$id'");
    }

    /*
     * Delete members detail for a particular member_id
     *
     */
    function deactivate ($id)
    {
        $query = "UPDATE inbox SET active = '0' WHERE id='$id'";
        return $this->query($query, false);
    }
    function activate ($id)
    {
        $query = "UPDATE inbox SET active = '1' WHERE id='$id'";
        return $this->query($query, false);
    }
    function get_failed($mid) {
		$sql="SELECT o.message_id,smsc,msisdn, m.message,o.member_id,o.group_id FROM sms.outbox o 
			  JOIN messages m ON m.id=o.message_id
			  WHERE1 message_id='$mid' AND status='pending'";
		return $this->select_all($sql);
    }	
    function update_failed($mid, $status='failure'){
             $this->query("UPDATE outbox set status ='$status' WHERE message_id='$mid' and status='pending'");
    }

}

?>
